package de.stefangoebel.aufgabenplaner;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.Iterator;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;

public class DatabaseHandler extends SQLiteOpenHelper {
    // All Static variables
    // Database Version
    private static final int DATABASE_VERSION = 1;
 
    // Database Name
    private static final String DATABASE_NAME = "Aufgabenplaner";
    
    private static final String TABLE_ID = "id";
    private static final String KEY_ID_ID = "id";
 
    private static final String TABLE_AUFGABEN = "tasks";
    private static final String KEY_AUFGABEN_ID = "id";
    private static final String KEY_AUFGABEN_DATE = "date";
    private static final String KEY_AUFGABEN_NAME = "name";
    private static final String KEY_AUFGABEN_CATEGORY = "category";
    private static final String KEY_AUFGABEN_PRIORITY = "priority";
    private static final String KEY_AUFGABEN_DURATION = "duration";
 
    public DatabaseHandler(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
 
    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {
    	String CREATE_ID_TABLE = "CREATE TABLE " + TABLE_ID + "("
    			+ KEY_ID_ID + " INTEGER)";
    	
    	db.execSQL(CREATE_ID_TABLE);
    	
    	String INSERT_ID_TABLE = "INSERT INTO " + TABLE_ID + "(" + KEY_ID_ID + ") VALUES (0)";
    	
    	db.execSQL(INSERT_ID_TABLE);
    	
        String CREATE_AUFGABEN_TABLE = "CREATE TABLE " + TABLE_AUFGABEN + "("
                + KEY_AUFGABEN_ID + " INTEGER PRIMARY KEY,"
                + KEY_AUFGABEN_DATE + " TEXT,"
        		+ KEY_AUFGABEN_NAME + " TEXT,"
                + KEY_AUFGABEN_CATEGORY + " TEXT,"
        		+ KEY_AUFGABEN_PRIORITY + " TEXT,"
        		+ KEY_AUFGABEN_DURATION + " INTEGER)";
        
        db.execSQL(CREATE_AUFGABEN_TABLE);
    }
 
    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
        //db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);
 
        // Create tables again
        //onCreate(db);
    }
    
    public int getNextId() {
    	int id;
    	SQLiteDatabase db = this.getReadableDatabase();
    	 
    	Cursor cursor = db.query(TABLE_ID, new String[] { KEY_ID_ID }, null, null, null, null, null, null);
    	if (cursor != null) {
    		cursor.moveToFirst();
    	}
    	
    	id = cursor.getInt(0) + 1;
    	
    	ContentValues values = new ContentValues();
    	values.put(KEY_ID_ID, id);
    	 
    	db.update(TABLE_ID, values, null, null);
    	
    	return id;
    }
    
    public Integer getTaskCount(Calendar date) {
    	SQLiteDatabase db = this.getReadableDatabase();
    	
    	Cursor cursor = db.rawQuery("SELECT COUNT(*) FROM " + TABLE_AUFGABEN + " WHERE " + KEY_AUFGABEN_DATE + "='" + Misc.getStringFromDate(date) + "'", null); 
        if (cursor.moveToFirst()) {
            return cursor.getInt(0);
        }
        
    	return 0;
    }
    
    public Aufgabe readTask(Context context, int id) {
    	Aufgabe ret = null;
    	
    	SQLiteDatabase db = this.getReadableDatabase();
   	 
    	Cursor cursor = db.query(TABLE_AUFGABEN, new String[] {KEY_AUFGABEN_NAME, KEY_AUFGABEN_DATE, KEY_AUFGABEN_CATEGORY, KEY_AUFGABEN_PRIORITY, KEY_AUFGABEN_DURATION}, KEY_AUFGABEN_ID + "=?", new String[] {String.valueOf(id)}, null, null, null, null);
    	if (cursor.moveToFirst()) {
    		ret = new Aufgabe(context, id, Misc.getDateFromString(cursor.getString(1)), cursor.getString(0), cursor.getString(2), cursor.getString(3), cursor.getInt(4));
    	}
    	
    	return ret;
    }
    
    public void writeTask(Aufgabe task) {
    	SQLiteDatabase db = this.getWritableDatabase();
    	
    	if (task.getId() == 0) {
    		task.setId(getNextId());
    	}
    	
    	String sql = "INSERT OR REPLACE INTO " + TABLE_AUFGABEN + "("
    			+ KEY_AUFGABEN_ID + ","
    			+ KEY_AUFGABEN_DATE + ","
    			+ KEY_AUFGABEN_NAME + ","
    			+ KEY_AUFGABEN_CATEGORY + ","
    			+ KEY_AUFGABEN_PRIORITY + ","
    			+ KEY_AUFGABEN_DURATION + ") VALUES ("
    			+ String.valueOf(task.getId()) + ","
    			+ "'" + Misc.getStringFromDate(task.getDate()) + "',"
    			+ "'" + task.getName() + "',"
    			+ "'" + task.getCategory() + "',"
    			+ "'" + task.getPriority() + "',"
    			+ task.getDuration() + ")";
    	
    	db.execSQL(sql);
    }
    
    public void deleteTask(int id) {
    	SQLiteDatabase db = this.getWritableDatabase();
    	
    	String sql = "DELETE FROM " + TABLE_AUFGABEN + " WHERE " + KEY_AUFGABEN_ID + "=" + String.valueOf(id);
    	db.execSQL(sql);
    }
    
    public Aufgabe[] readTasksByDate(Context context, Calendar date) {
    	ArrayList<Aufgabe> list = new ArrayList<Aufgabe>();
    	
    	SQLiteDatabase db = this.getReadableDatabase();
   	 
    	Cursor cursor = db.query(TABLE_AUFGABEN, new String[] {KEY_AUFGABEN_ID, KEY_AUFGABEN_NAME, KEY_AUFGABEN_DATE, KEY_AUFGABEN_CATEGORY, KEY_AUFGABEN_PRIORITY, KEY_AUFGABEN_DURATION}, KEY_AUFGABEN_DATE + "=?", new String[] {Misc.getStringFromDate(date)}, null, null, null, null);
    	if (cursor.moveToFirst()) {
    		do {
    			list.add(new Aufgabe(context, cursor.getInt(0), Misc.getDateFromString(cursor.getString(2)), cursor.getString(1), cursor.getString(3), cursor.getString(4), cursor.getInt(5)));
    		} while (cursor.moveToNext());
    	}
    	
    	Aufgabe[] ret = new Aufgabe[list.size()];
        Iterator<Aufgabe> iterator = list.iterator();
        for (int i = 0; i < ret.length; i++) {
            ret[i] = iterator.next();
        }
    	return ret;
    }
}

